package com.cn.ccp.service.faculty;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.cn.ccp.bean.TeachingGroupBean;
import com.cn.ccp.service.BasicService;

public class TeachingGroupService  extends BasicService{

	public boolean insert(TeachingGroupBean bean){
		String sql = "insert into teaching_group(teaching_group_name ,teaching_group_des,faculity_id)values(?,?,?)";
		Object[] objs = new Object[]{bean.getTeaching_group_name() ,bean.getTeaching_group_des() ,bean.getFaculity_id()};
		return jdbcTemplate.update(sql,objs) > 0;
	}
	
	
	public boolean update(TeachingGroupBean bean){
		String sql = "update teaching_group set teaching_group_name = ? ,teaching_group_des  = ?,faculity_id  = ? where id = ?";
		Object[] objs = new Object[]{bean.getTeaching_group_name() ,bean.getTeaching_group_des() ,bean.getFaculity_id() ,bean.getId()};
		return jdbcTemplate.update(sql,objs) > 0;
	}
	
	public boolean delete(int id){
		String sql = "delete from teaching_group where id = ?";
		Object[] objs = new Object[]{id};
		return jdbcTemplate.update(sql,objs) > 0;
	}
	
	public int getCount(){
		String sql = "SELECT count(1) FROM teaching_group  ";
		return jdbcTemplate.queryForInt(sql) ;
	}
	
	public List<TeachingGroupBean> getTeachingGroupList(int start,int pagesize){
		String sql = "SELECT tg.* ,f.facultyname FROM teaching_group tg "+
		" LEFT OUTER JOIN faculty f ON f.id = tg.faculity_id "+
		" limit ?,?";
		Object[] objs = new Object[]{start ,pagesize};
		return jdbcTemplate.query(sql, objs ,new TeachingGroupRowMapper());
	}
	
	/**
	 * 根据学院id查询教研组列表
	 * @return
	 */
	public List<TeachingGroupBean> getTeachingGroupListByFId(String facultyId){
		String sql = "SELECT tg.*  FROM teaching_group tg where tg.faculity_id  = ? ";
		Object[] objs = new Object[]{facultyId};
		return jdbcTemplate.query(sql, objs ,new TeachingGroupRowMapper());
	}
	
	public int showBeanByName(String name){
		String sql = "SELECT tg.id FROM teaching_group tg "+
					" LEFT OUTER JOIN faculty f ON f.id = tg.faculity_id "+
					" WHERE tg.teaching_group_name = ?";
		Object[] objs = new Object[]{name};
		return (Integer) jdbcTemplate.queryForObject(sql, objs , Integer.class);
	}
	
	
	public TeachingGroupBean showBean(int id){
		String sql = "SELECT tg.* ,f.facultyname FROM teaching_group tg "+
					" LEFT OUTER JOIN faculty f ON f.id = tg.faculity_id "+
					" WHERE tg.id = ?";
		Object[] objs = new Object[]{id};
		return (TeachingGroupBean) jdbcTemplate.queryForObject(sql, objs , new TeachingGroupRowMapper());
	}
	
	public List<TeachingGroupBean> getTeachingGroupListByUId(String uid){
		String sql = "SELECT tg.* FROM teaching_group tg "+
					" LEFT OUTER JOIN user_teaching_group utg ON utg.teaching_group_id = tg.id "+
					" WHERE utg.user_id = ?";
		Object[] objs = new Object[]{uid};
		return jdbcTemplate.query(sql, objs ,new TeachingGroupRowMapper());
	}
	
	
	public class  TeachingGroupRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			TeachingGroupBean bean = new  TeachingGroupBean();
			bean.setFaculity_id(rs.getString("faculity_id"));
			bean.setId(rs.getInt("id"));
			bean.setTeaching_group_des(rs.getString("teaching_group_des"));
			bean.setTeaching_group_name(rs.getString("teaching_group_name"));
			try{
				bean.setFaculity_name(rs.getString("facultyname"));
			}catch(Exception e){}
			return bean;
		}
	}
}
